package com.ofek.eventos.serverside.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
/**
 * a class to help with sql commands
 * @author <p>SONA, Sasha,Ofek,Nitzan and Avihai, Group 6,<br>Laboratory in Software Engineering,Ort Braude College.</p>
 *
 */
public class SQLHelper {
	/**
	 * helper to send a use command to sql
	 * @param con
	 * @param dbName
	 * @throws SQLException
	 */
	public static void use(final Connection con,final String dbName) throws SQLException {
		final Statement stmt = con.createStatement();
		stmt.execute("USE " + dbName);
		stmt.close();
	}
	/**
	 * helper to send an insert command to sql database
	 * @param con
	 * @param tableName
	 * @param values
	 * @throws SQLException
	 */
	public static void insert(final Connection con,final String tableName,final String... values) throws SQLException {
		final String sql = "INSERT INTO "+ tableName + " VALUES "+ flatValues(values) + ";";
		con.prepareStatement(sql).executeUpdate();
	}
	/**
	 * helper to send an insert command to sql database
	 * @param con
	 * @param tableName
	 * @param fields
	 * @param values
	 * @throws SQLException
	 */
	public static void insert(final Connection con,final String tableName,final String[] fields,final String[] values) throws SQLException {
		final String sql = "INSERT INTO "+ tableName + flatFields(fields) +" VALUES "+flatValues(values) + ";";
		con.prepareStatement(sql).executeUpdate();
	}
	/**
	 * 
	 * @param strings
	 * @return ( strings[0],...,strings[n] )
	 */
	private static String flatFields(final String[] strings) {
		String flatten = " ( ";
		for (int i=0;i< strings.length-1 ;i++) 
			flatten += strings[i] + ",";
		return flatten+=strings[strings.length-1] + " ) ";
	}
	/**
	 * 
	 * @param strings
	 * @return ( 'strings[0]',...,'strings[n]' )
	 */
	private static String flatValues(final String[] strings) {
		String flatten = " ( ";
		for (int i=0;i< strings.length-1 ;i++) 
			flatten += "'" + strings[i] + "' , ";
		return flatten+="'"+strings[strings.length-1]+"' ) ";
	}
	/**
	 * helper to send a select command to sql database 
	 * @param con
	 * @param tableName
	 * @param field
	 * @return ArrayList<String>
	 * @throws SQLException
	 */
	public static ArrayList<String> select(final Connection con,final String tableName,final String field) throws SQLException {
		final String sql = "select "+field+" from " +tableName + ";";
		final ResultSet res = con.prepareStatement(sql).executeQuery();
		final ArrayList<String> results = new ArrayList<String>();
		while (res.next())
			results.add(res.getString(field));
		return results;
	}
	/**
	 * 
	 * @param con
	 * @param tableName
	 * @param where - exemple : "where rid='9' and uid ='ofek'"
	 * @param fields
	 * @return results
	 * @throws SQLException
	 */
	public static ArrayList<HashMap<String, String>> select(final String tableName,final String where,final Connection con,final String... fields) throws SQLException {
		final String sql = "select "+fieldsToProjection(fields)+" from " +tableName + (where!=null && where.length()>0 ? " where " + where+";" : "");
		final ResultSet res = con.prepareStatement(sql).executeQuery();
		final ArrayList<HashMap<String,String>> results = new ArrayList<HashMap<String,String>>();
		while (res.next()) {
			final HashMap<String, String> hash = new HashMap<String, String>();
			for (final String field : fields)  hash.put(field,res.getString(field));
			results.add(hash);
		}
		return results;
	}
	/**
	 * helper to send a select command to sql database 
	 * @param con
	 * @param tableName
	 * @param fields
	 * @return ArrayList<HashMap<String, String>>
	 * @throws SQLException
	 */
	public static ArrayList<HashMap<String, String>> select(final Connection con,final String tableName,final String... fields) throws SQLException {
		return select(tableName,"",con,fields);
	}
	private static String fieldsToProjection(final String[] fields) {
		final StringBuilder sb = new StringBuilder();
		for (final String f : fields) sb.append(f).append(',');
		sb.deleteCharAt(sb.length()-1);
		return sb.toString();
	}
}
